use shop; 
drop table if exists product_type;

create table product_type(
    product_type varchar(32) not null,
    max_sale_price double default 0,
    min_purchase_price double default 0,
    avg_profit double default 0
    );
    
insert into product_type 
select product_type, max(sale_price), min(purchase_price), avg(sale_price-purchase_price) 
from product group by product_type;



-- 事务：保证数据完整性
begin;

commit;

rollback;
